package com.dao;

import com.bean.Course;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CourseDao {
    //获取学生信息列表
    public static List<Course> getList() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Course> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("SELECT * FROM course");
            rs = stmt.executeQuery();
            while (rs.next()) {
                Course cou = new Course();
                cou.setCourse_id(rs.getString(1));
                cou.setCourse_name(rs.getString(2));
                cou.setYear_semester(rs.getString(3));

                list.add(cou);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
        return list;
    }

    //增加学生信息
    public static void addCourse(Course course){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("INSERT INTO course (course_id,course_name,year_semester) VALUES (?,?,?);");
            stmt.setString(1, course.getCourse_id);
            stmt.setString(2, course.getCourse_name);
            stmt.setString(3, course.getYear_semester);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }

    //根据学号查找学生信息
    public static Course searchCourse(String id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rst = null;
        Course cou = new Course();
        try{
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement("SELECT * FROM course WHERE student_id=?");
            pstmt.setString(1,id);
            rst = pstmt.executeQuery();
            if(rst.next()){
                cou.setCourse_id(rst.getString("course_id"));
                cou.setCourse_name(rst.getString("course_name"));
                cou.setYear_semester(rst.getString("year_semester"));

                return cou;
            }else{
                return  null;
            }
        }catch(SQLException se){
            return null;
        }finally{
            try{
                conn.close();
            }catch(SQLException se){
            }
        }
    }




    //修改功能
    public static void updateCourse(Course course) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("UPDATE course SET course_id=?,course_name=?,year_semester=? where course_id=?");
            stmt.setString(1, course.getCourse_id());
            stmt.setString(2, course.getCourse_name());
            stmt.setString(3, course.getYear_semester());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }

    //删除功能
    public static void deleteData(String id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("DELETE FROM course WHERE course_id=?;");
            stmt.setString(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }
}